#TODO remove close games, 52-48 or closer

__author__ = 'william'
__author__ = 'william'
import sys, psycopg2, math, collections


print "Tipping calculator"
print "Enter three digit code\n"

conn_string = "dbname='calcdb' user='william'"
conn = None

try:
    conn = psycopg2.connect(conn_string)
except:
    print "I am unable to connect bro"

cur = conn.cursor()

def getPosition(team):
    query_string = "SELECT * FROM wk18ladder WHERE team='%s'" % team
    cur.execute(query_string)
    rows = cur.fetchall()
    position = 1.0
    position1 = 1.0
    position2 = 1.0

    for row in rows:
        position1 = float(row[6])
        position2 = float(row[7])
        position = (position1/position2)*100.0

    return position

def getDefenceAndAttack(team):
    query_string = "SELECT * FROM wk18ladder WHERE team='%s'" % team
    cur.execute(query_string)
    rows = cur.fetchall()
    pointsfor = 0.0
    pointsagainst = 0.0
    for row in rows:
        pointsfor = int(row[6])
        pointsagainst = int(row[7])

    return (pointsfor, pointsagainst)

def getScore(team):
    query_string = "SELECT * FROM teamcomparison WHERE team='%s'" % team
    cur.execute(query_string)
    rows = cur.fetchall()
    score = 0
    for row in rows:
        score = row[1]
    return score

def teamBettingPercentage(teamabetting, teambbetting):
    teampercentage = (teamabetting / (teamabetting + teambbetting))*1.0
    return teampercentage

def getHomeWinTotal():
    query_string = "select count(*) from oldhistory where ((home='%s' and away='%s' and homescore>awayscore) or (away='%s' and home='%s' and homescore<awayscore))" % (teama, teamb, teamb, teama)
    cur.execute(query_string)
    rows = cur.fetchone()
    homewin = rows[0]
    return homewin

def getCoachScore(team):
    query_string = "SELECT * FROM coaches WHERE team='%s'" % team
    cur.execute(query_string)
    rows = cur.fetchall()
    score = 0.0
    for row in rows:
        score = float(row[1])
    return score

def getHomeLoseTotal():
    query_string = "select count(*) from oldhistory where ((home='%s' and away='%s' and homescore<awayscore) or (away='%s' and home='%s' and homescore>awayscore))" % (teamb, teama, teama, teamb)
    cur.execute(query_string)
    rows = cur.fetchone()
    homelose = rows[0]
    return homelose

def getHomeWinAtHome():
    query_string = "select count(*) from oldhistory where (home='%s' and away='%s' and homescore>awayscore)" % (teama, teamb)
    cur.execute(query_string)
    rows = cur.fetchone()
    homewinathome = rows[0]
    return homewinathome

def getHomePlayedTotal():
    query_string = "select count(*) from oldhistory where home='%s' and away='%s'" % (teama, teamb)
    cur.execute(query_string)
    rows = cur.fetchone()
    homeplayedtotal = rows[0]
    return homeplayedtotal

def getPlayedTotal():
    query_string = "select count(*) from oldhistory where (home='%s' and away='%s') or (home='%s' and away='%s')" % (teama, teamb, teamb, teama)
    cur.execute(query_string)
    rows = cur.fetchone()
    playedtotal = rows[0]
    return playedtotal

def calculateWinner(teama, teamb):
    ##Part 3 - Attack v defense  TO BE COMPLETED
    #teamaattack, teamadefence = getDefenceAndAttack(teama)
    #teambattack, teambdefence = getDefenceAndAttack(teama)


    ##Part 1 - history
    totalgames = float(getHomeWinTotal()) + float(getHomeLoseTotal())
    hometotalratio = float(getHomeWinTotal())/totalgames
    homewinratio = float(getHomeWinAtHome())/float(getHomePlayedTotal())
    teamaratio = round((homewinratio*0.4) + (hometotalratio*0.6), 2)
    teambratio = 1 - teamaratio

    ##PART 2 - position on ladder code and team score rating
    teamaposition = (float(getPosition(teama)))
    teambposition = (float(getPosition(teamb)))

    teamascore = (float(getScore(teama)))
    teambscore = (float(getScore(teamb)))

    ##attack and defence code
    teamaattack, teamadefence = getDefenceAndAttack(teama)
    teambattack, teambdefence = getDefenceAndAttack(teamb)

    teamaatratio = float(teamaattack/(teamaattack+teambattack))
    teambatratio = float(teambattack/(teamaattack+teambattack))
    teamadfratio = float(teamadefence/(teamadefence+teambdefence))
    teambdfratio = float(teambdefence/(teamadefence+teambdefence))

    teamapointstats = float((teamaatratio + teamadfratio) / 2.0)
    teambpointstats = float((teambatratio + teambdfratio) / 2.0)

    ##calcuating ladder position scores
    if teamaposition < teambposition:
        teamapositionbetting = teamBettingPercentage(teamaposition, teambposition)
        teambpositionbetting = 1-teamapositionbetting
    else:
        teambpositionbetting = teamBettingPercentage(teambposition, teamaposition)
        teamapositionbetting = 1-teambpositionbetting

    #calculating team overall score
    if teamascore < teambscore:
        teamascorebetting = teamBettingPercentage(teamascore, teambscore)
        teambscorebetting = 1-teamascorebetting
    else:
        teambscorebetting = teamBettingPercentage(teambscore, teamascore)
        teamascorebetting = 1-teambscorebetting

    #coaches data
    teamacoach = float(getCoachScore(teama))
    teambcoach = float(getCoachScore(teamb))

    teamafinal = round((teamascorebetting*0.4) + (teamapositionbetting*0.05) + (teamaratio*0.05) + (teamapointstats*0.5) + (teamacoach*0.75), 2)
    teambfinal = round((teambscorebetting*0.4) + (teambpositionbetting*0.05) + (teambratio*0.05) + (teambpointstats*0.5) + (teambcoach*0.75), 2)

    return (teamafinal, teambfinal)

def setTeams():
    team = raw_input("Enter Team: ")
    return team

def writeGraph(gamenumber, accountbalance):
    filename = 'graph.csv'
    outputgraph = open(filename, 'a')
    line1 = "%s,%s\n" % (gamenumber, accountbalance)
    outputgraph.write(line1)
    outputgraph.close()

def resultChecker(teamafinal, teambfinal):
    if teamafinal > teambfinal:
        result = teamafinal - teambfinal
        if result < 0.01:
            return True
        else:
            return False
    else:
        result = teambfinal - teamafinal
        if result < 0.01:
            return True
        else:
            return False

query_string = "select * from results"
cur.execute(query_string)
rows = cur.fetchall()

homelist = list()
awaylist = list()
winnerlist = list()
homeoddslist = list()
awayoddslist = list()


for row in rows:
    homelist.append(row[2])
    awaylist.append(row[3])
    homeoddslist.append(row[6])
    awayoddslist.append(row[8])
    winnerlist.append(row[9])

correctamount = 0.00
incorrectamount = 0.00
bettingbalance = 100.00
startingbankaccount = bettingbalance
betamount = 0.00
bet = 0.00

##2012 - 0-143
##2009-2012 - 0-740

for x in range(0, 140):

    teama = homelist[x]
    teamb = awaylist[x]
    teamabettingodds = homeoddslist[x]
    teambbettingodds = awayoddslist[x]
    teamafinal, teambfinal = calculateWinner(teama, teamb)
    winner = winnerlist[x]

    if teamafinal > teambfinal:
        betamount = (bettingbalance / 50) + 5.0
        bettingbalance -= betamount
        bet = teamabettingodds * betamount

        if teama == winner:
            answer = "Crct"
            correctamount += 1
            bettingbalance += bet
        else:
            answer = "Wrng"
            incorrectamount += 1
    else:
        betamount = (bettingbalance / 50) + 5.0
        bettingbalance -= betamount
        bet = teamabettingodds * betamount
        if teamb == winner:
               answer = "Crct"
               correctamount += 1
               bettingbalance += bet
        else:
               answer = "Wrng"
               incorrectamount += 1

    writeGraph(x, bettingbalance)

    print "%s - %s: %s   %s: %s  Winner:%s   Answer: %s     Bet (odds): %s (a: %s, b: %s)     Balance: %s" % (x,teama, teamafinal, teamb, teambfinal, winner, answer, round(betamount, 2), round(teamabettingodds, 2), round(teambbettingodds, 2), round(bettingbalance, 2))




percentage = round((correctamount/(correctamount+incorrectamount)) * 100.00, 2)

print "\n%s games correct, %s games wrong" % (correctamount, incorrectamount)
print "Percentage win rate: %s" % percentage
print "Balance started at $100, starting bet at $4.5 and balance is now... $%s\n" % round(bettingbalance, 2)
print "Total amount made over %s games is %s" % (x, round(bettingbalance-startingbankaccount,2))














